<?php

include('../../include/functions.inc.php');
$list = $db->query_select("cllg_career", "FK_ENTITY=" . $user->entity);
if (!isEmpty(getParam('operation'))) {
    switch (getParam('operation')) {
        case 'id':
            loadCta($db, 'ID=' . getParam('ID'), $user);
            break;
        case 'cargos':
            cargos();
            break;
        case 'abonos':
            abonos();
            break;
        case 'stud':
            listStudents();
            break;
    }
}

function listStudents() {
    header('Location: ../students/slistStudents.php');
}

function loadCta($db, $where, $user) {
    $r = $db->query_select("cllg_student", $where);
    $now = date(FORMAT);

    if (count($r) > 0) {
        $c = $r[0]; //datos estudiante
        $datos = array('NAME' => $c['FIRTS_NAME'] . ',' . $c['LAST_NAME'],
            'ID' => $c['ID'], 'AIU' => ($c['AIU_CODE'] != NULL) ? $c['AIU_CODE'] : "");
        $r2 = $db->query_select("cllg_account", "FK_STUDENT=" . $c["ID"] . " AND FK_ENTITY_OWNER=" . $user->entity);

        if (count($r2) > 0) {
            $r2 = $r2[0];
            $datos["ACCOUNT"] = $r2["ID"];
            $queryVencidos = sprintf("SELECT SUM(TOT) TOTAL FROM (
                SELECT CREATED,DESCRIPTION,DATE_LIMIT,(AMOUNT+PENALTY) TOT FROM cllg_cargo
                WHERE DATE_PAYED IS NULL AND TOTAL IS NULL AND FK_ACCOUNT=%s
                UNION
                SELECT CREATED,DESCRIPTION,DATE_LIMIT,(TOTAL_PENDIENTE) TOT FROM cllg_cargo
                       WHERE DATE_PAYED IS NULL AND TOTAL>0 AND FK_ACCOUNT=%s ) B
                WHERE  B.DATE_LIMIT<%s", $r2["ID"], $r2["ID"], sqlValue($now, "date"));
            
            $queryPendientes = sprintf("SELECT  STUD, sum(stotal) TOTAL FROM(
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.AMOUNT) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT 
                        AND     cc.FK_STUDENT=%s
                        AND     ISNULL(cllg_cargo.DATE_PAYED)
                        AND     ISNULL(cllg_cargo.TOTAL_PENDIENTE)
                        GROUP   BY STUD
                        UNION
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.TOTAL_PENDIENTE) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT
                        AND     cc.FK_STUDENT =%s 
                        AND     cllg_cargo.TOTAL_PENDIENTE > 0
                        GROUP   BY STUD
                        UNION
                        SELECT  cllg_cargo.FK_ACCOUNT AS STUD,
                                SUM(cllg_cargo.PENALTY) AS stotal
                        FROM    cllg_cargo,
                                cllg_account cc
                        WHERE   cc.ID = cllg_cargo.FK_ACCOUNT 
                        AND     cc.FK_STUDENT=%s
                        AND     ISNULL(cllg_cargo.DATE_PAYED)
                        AND     cllg_cargo.DATE_LIMIT < NOW()
                        AND     ISNULL(cllg_cargo.TOTAL_PENDIENTE)
                        GROUP   BY STUD
                        ORDER   BY  STUD) DEUDAS GROUP BY STUD", $c["ID"], $c["ID"], $c["ID"]);
            
            $rsV = $db->query_toArray($queryVencidos);
            $rsV = $rsV[0];
            $rsP = $db->query_toArray($queryPendientes);
            $rsP = $rsP[0];
            $datos["SALDO_VENCIDO"] = $rsV["TOTAL"] != NULL ? number_format($rsV["TOTAL"], 2) : 0.00;
            $datos["SALDO_PENDIENTE"] = $rsP["TOTAL"] != NULL ? number_format($rsP["TOTAL"], 2) : 0.00;
            $datos["CHEQUE_RECHAZADO"] = 0;
            $strQuery = "SELECT  cllg_account.ID FK_ACCOUNT,
                                cllg_cheque.NUMERO 
                        FROM    cllg_cheque,
                                cllg_factura,
                                cllg_account
                        WHERE   cllg_account.ID = " . $r2["ID"] . "
                        AND     cllg_cheque.STATUS = 0
                        AND     cllg_cheque.FECHA_REPOSICION IS NULL
                        AND     cllg_cheque.FK_FACURA = cllg_factura.ID
                        AND     cllg_factura.FK_STUDENT = cllg_account.FK_STUDENT";
            $arrInfo = $db->query_toArray($strQuery);
            if (isset($arrInfo[0])) {
                $datos["CHEQUE_RECHAZADO"] = 1;
            }
        } else {
            $datos["ERROR"] = "EL ESTUDIANTE NO POSEE CUENTA CORRIENTE EN ESTA ENTIDAD!";
        }
        $_SESSION['pack'] = serialize($datos);
    } else {
        $datos = array('ERROR' => 'NO EXISTE NINGUN ESTUDIANTE CON ESE CODIGO!');
        $_SESSION['pack'] = serialize($datos);
    }
    header('Location: ../../include/transport.php');
}

function cargos() {
    header('Location: cfgCargos.php?ACCOUNT=' . getParam("ACCOUNT"));
}

function abonos() {
    header('Location: cfgCargosPen.php?ACCOUNT=' . getParam("ACCOUNT"));
}

?>